package com.ngc.mall.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ngc.mall.domain.OrderDomain;
import com.ngc.mall.vo.OrderSummaryVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface OrderMapper extends BaseMapper<OrderDomain> {

    ///查询今天的总销售额，总销售数
    @Select({"SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') as date, COALESCE(COUNT(*), 0) as totalCount,COALESCE(SUM(final_pay_amount), 0) as totalAmount FROM `order` WHERE DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')>=DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') and DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')<=DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59');"})
    OrderSummaryVO queryToday();

    ///查询昨天的总销售额，总销售数
    @Select({"select DATE_FORMAT(date_sub(NOW(),interval 1 day),'%Y-%m-%d'),COALESCE(COUNT(*), 0) as totalCount,COALESCE(SUM(final_pay_amount), 0) as totalAmount from `order` where DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(date_sub(NOW(),interval 1 day),'%Y-%m-%d')"})
    OrderSummaryVO queryYesterday();

    ///查询今天0点-23点，每小时的总销售额，总销售数
    @Select({"SELECT DATE_FORMAT(create_time,'%k')as date, COALESCE(COUNT(*), 0) as totalCount,COALESCE(SUM(final_pay_amount), 0) as totalAmount FROM `order` WHERE DATE_FORMAT(create_time,'%Y-%m-%d')=DATE_FORMAT(NOW(), '%Y-%m-%d')group by date;"})
    List<OrderSummaryVO> queryTodayForHour();

    ///查询本周的总销售额，总销售数
    @Select({"select YEARWEEK(now(),1) as date,COALESCE(SUM(final_pay_amount), 0) as totalAmount,COALESCE(COUNT(*), 0) as totalCount from `order` WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1)"})
    OrderSummaryVO queryWeek();

    ///查询上周的总销售额，总销售数
    @Select({"select YEARWEEK(now(),1)-1 as date,COALESCE(SUM(final_pay_amount), 0) as totalAmount,COALESCE(COUNT(*), 0) as totalCount from `order` WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1)"})
    OrderSummaryVO queryLastWeek();

    ///查询本周每天的销售额，总销售数
    @Select({"SELECT \n" +
            "\tCASE DATE_FORMAT(create_time,'%w')\n" +
            "\t\t  WHEN 1 THEN '周一'\n" +
            "          WHEN 2 THEN '周二'\n" +
            "          WHEN 3 THEN '周三'\n" +
            "          WHEN 4 THEN '周四'\n" +
            "          WHEN 5 THEN '周五'\n" +
            "          WHEN 6 THEN '周六'\n" +
            "          WHEN 0 THEN '周日'\n" +
            "    END as date,\n" +
            "    COALESCE(SUM(final_pay_amount), 0) as totalAmount,\n" +
            "\t\t COALESCE(COUNT(*), 0) as totalCount\n" +
            "FROM `order`\n" +
            "WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1)\n" +
            "GROUP BY date"})
    List<OrderSummaryVO> queryWeekForDay();

    ///查询本月的总销售额，总销售数
    @Select({"SELECT MONTH(CURRENT_DATE()) as date,sum(final_pay_amount) totalAmount,count(*) totalCount FROM `order` WHERE MONTH(create_time) = MONTH(CURRENT_DATE()) group by MONTH(CURRENT_DATE());"})
    OrderSummaryVO queryMonth();

    ///查询上个月的总销售额，总销售数
    @Select({"SELECT MONTH(CURRENT_DATE()) - 1 as date, sum(final_pay_amount) totalAmount,count(*) totalCount FROM `order` WHERE MONTH(create_time) = MONTH(CURRENT_DATE()) - 1 group by MONTH(CURRENT_DATE()) - 1;"})
    OrderSummaryVO queryLastMonth();

    ///查询本月每天的总销售额，总销售数
    @Select({"SELECT DATE_FORMAT(create_time,'%e') as date,sum(final_pay_amount) totalAmount,count(*) totalCount FROM `order` WHERE MONTH(create_time) = MONTH(CURRENT_DATE()) group by DATE_FORMAT(create_time,'%e');"})
    List<OrderSummaryVO> queryMonthForDay();

    ///查询本季度的总销售额，总销售数
    @Select({"select QUARTER(CURRENT_DATE()) as date,sum(final_pay_amount) totalAmount,count(*) totalCount from `order` where QUARTER(create_time) = QUARTER(CURRENT_DATE()) group by QUARTER(CURRENT_DATE());"})
    OrderSummaryVO queryQuarter();

    ///查询上季度的总销售额，总销售数
    @Select({"select QUARTER(CURRENT_DATE())-1 as date,sum(final_pay_amount) totalAmount,count(*) totalCount from `order` where QUARTER(create_time) = QUARTER(CURRENT_DATE())-1 group by QUARTER(CURRENT_DATE())-1;"})
    OrderSummaryVO queryLastQuarter();

    ///查询本季度每个月的总销售额，总销售数
    @Select({"select DATE_FORMAT(create_time,'%c') as date,sum(final_pay_amount) totalAmount,count(*) totalCount from `order` where QUARTER(create_time) = QUARTER(CURRENT_DATE()) group by DATE_FORMAT(create_time,'%c');"})
    List<OrderSummaryVO> queryQuarterForMonth();

    ///查询今年的总销售额，总销售数
    @Select({"select YEAR(CURRENT_DATE()) as date,sum(final_pay_amount) totalAmount,count(*) totalCount from `order` where YEAR(create_time) = YEAR(CURRENT_DATE()) group by YEAR(CURRENT_DATE())"})
    OrderSummaryVO queryYear();

    ///查询去年的总销售额，总销售数
    @Select({"select YEAR(CURRENT_DATE())-1 as date,sum(final_pay_amount) totalAmount,count(*) totalCount from `order`WHERE YEAR(create_time) = YEAR(CURRENT_DATE())-1 group by YEAR(CURRENT_DATE())-1"})
    OrderSummaryVO queryLastYear();

    ///查询今年每个月的总销售额，总销售数
    @Select({"select DATE_FORMAT(create_time,'%c') as date,sum(final_pay_amount) totalAmount,count(*) totalCount from `order` where YEAR(create_time) = YEAR(CURRENT_DATE()) group by DATE_FORMAT(create_time,'%c')"})
    List<OrderSummaryVO> queryYearForMonth();
}
